--This is a Script file that creates all the tables needed for the program.

--------------------------------------------------------------------------------
--Developer Name: Jason Lyle
--Functionality: Relation for the profile manager files.
CREATE TABLE PM_Profile_Manager(
  --PRIMARY KEY columns
    Profile_Manager_id VARCHAR2(32) DEFAULT Sys_Guid() PRIMARY KEY
  --OTHER columns
  , file_name VARCHAR2(50) UNIQUE NOT NULL
  --FOREIGN KEY columns
  --STANDARD LEAD columns
  , std_dt_created TIMESTAMP DEFAULT systimestamp
  , std_dt_updated TIMESTAMP DEFAULT systimestamp
  , std_user_created VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  , std_user_updated VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  --constraints
);

--Developer Name: Jason Lyle
--Functionality: Triggers to automatically generate a primary key
--	and to set the date updated column
--Table and Column comments
--Triggers
CREATE OR REPLACE TRIGGER PM_Profile_Manager_bi
BEFORE INSERT ON PM_Profile_Manager
FOR EACH ROW
WHEN (new.Profile_Manager_id IS NULL)
BEGIN
  SELECT Sys_Guid() INTO :new.Profile_Manager_id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER PM_Profile_Manager_bu
BEFORE INSERT ON PM_Profile_Manager
FOR EACH ROW
BEGIN
  SELECT SYSTIMESTAMP INTO :new.std_dt_updated FROM dual;
END;
/

--------------------------------------------------------------------------------
--Developer Name: Jason Lyle
--Functionality: A relation to hold the team profile entities.
CREATE TABLE PM_Team_Profile(
  --PRIMARY KEY columns
    Team_Profile_id VARCHAR2(32) DEFAULT Sys_Guid() PRIMARY KEY
  --OTHER columns
  --Basic information
  , team_name VARCHAR2(100) NOT NULL
  , picture BLOB
  , school_name VARCHAR2(100)
  , conference VARCHAR2(50)
  , cheer VARCHAR2(4000)
  , notes VARCHAR2(4000)
  --Team statistics
  , wins NUMBER
  , losses NUMBER
  , win_loss_score_diff NUMBER
  , espn_ranking NUMBER
  , rpi NUMBER
  --System information
  , text_color VARCHAR2(7) DEFAULT 'black' CHECK (text_color IN ('blue', 'red', 
  		'yellow', 'black', 'magenta', 'green'))
  , to_update NUMBER DEFAULT 0 CHECK (to_update IN (0, 1))
  , to_delete NUMBER DEFAULT 0 CHECK (to_delete IN (0, 1))
  --FOREIGN KEY columns
  , profile_manager_id VARCHAR2(32) NOT NULL 
  		REFERENCES PM_Profile_Manager(profile_manager_id)
  --STANDARD LEAD columns
  , std_dt_created TIMESTAMP DEFAULT systimestamp
  , std_dt_updated TIMESTAMP DEFAULT systimestamp
  , std_user_created VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  , std_user_updated VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  --constraints
);

--Developer Name: Jason Lyle
--Functionality: Triggers to automatically create the primary key and set the
--	date updated column.
--Table and Column comments
--Triggers
CREATE OR REPLACE TRIGGER PM_Team_Profile_bi
BEFORE INSERT ON PM_Team_Profile
FOR EACH ROW
WHEN (new.Team_Profile_id IS NULL)
BEGIN
  SELECT Sys_Guid() INTO :new.Team_Profile_id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER PM_Team_Profile_bu
BEFORE INSERT ON PM_Team_Profile
FOR EACH ROW
BEGIN
  SELECT SYSTIMESTAMP INTO :new.std_dt_updated FROM dual;
END;
/

--------------------------------------------------------------------------------
--Developer Name: Jason Lyle
--Functionality: A relation to hold the athlete entities
CREATE TABLE PM_Athlete_Profile(
  --PRIMARY KEY columns
    Athlete_Profile_id VARCHAR2(32) DEFAULT Sys_Guid() PRIMARY KEY
  --OTHER columns
  --Basic information
  , first_name VARCHAR2(50) NOT NULL
  , middle_initial CHAR
  , last_name VARCHAR2(50) NOT NULL
  , nickname VARCHAR2(50)
  , picture BLOB
  , class_rank CHAR(2) CHECK (class_rank IN ('fr', 'so', 'jr', 'sr', 'ss',
  		'gr', 'ot'))
  , notes VARCHAR2(4000)
  --Player statistics
  , points_per_game NUMBER
  , assists_per_game NUMBER
  , rebounds_per_game NUMBER
  , mins_per_game NUMBER
  , steals_per_game NUMBER
  , free_throw_percentage NUMBER
  --System information
  , text_color VARCHAR2(7) DEFAULT 'black' CHECK (text_color IN ('blue', 'red',
  		'yellow', 'black', 'magenta', 'green'))
  , to_update NUMBER DEFAULT 0 CHECK (to_update IN (0, 1))
  , to_delete NUMBER DEFAULT 0 CHECK (to_delete IN (0, 1))
  --FOREIGN KEY columns
  , profile_manager_id VARCHAR2(32) NOT NULL
  		REFERENCES PM_Profile_Manager(profile_manager_id)
  --STANDARD LEAD columns
  , std_dt_created TIMESTAMP DEFAULT systimestamp
  , std_dt_updated TIMESTAMP DEFAULT systimestamp
  , std_user_created VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  , std_user_updated VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  --constraints
);

--Developer Name: Jason Lyle
--Functionality: Triggers to automatically create the primary key and set the
--	date updated column
--Table and Column comments
--Triggers
CREATE OR REPLACE TRIGGER PM_Athlete_Profile_bi
BEFORE INSERT ON PM_Athlete_Profile
FOR EACH ROW
WHEN (new.Athlete_Profile_id IS NULL)
BEGIN
  SELECT Sys_Guid() INTO :new.Athlete_Profile_id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER PM_Athlete_Profile_bu
BEFORE INSERT ON PM_Athlete_Profile
FOR EACH ROW
BEGIN
  SELECT SYSTIMESTAMP INTO :new.std_dt_updated FROM dual;
END;
/

--------------------------------------------------------------------------------
--Developer Name: Jason Lyle
--Functionality: A relation to hold the relationship between a team and an
--	athlete.
CREATE TABLE PM_Team_Athlete(
  --PRIMARY KEY columns
    Team_Athlete_id VARCHAR2(32) DEFAULT Sys_Guid() PRIMARY KEY
  --OTHER columns
  , athlete_number NUMBER
  --System information
  , to_update NUMBER DEFAULT 0 CHECK (to_update IN (0, 1))
  , to_delete NUMBER DEFAULT 0 CHECK (to_delete IN (0, 1))
  --FOREIGN KEY columns
  , team_profile_id VARCHAR2(32) NOT NULL
  , athlete_profile_id VARCHAR2(32) NOT NULL 
  --STANDARD LEAD columns
  , std_dt_created TIMESTAMP DEFAULT systimestamp
  , std_dt_updated TIMESTAMP DEFAULT systimestamp
  , std_user_created VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  , std_user_updated VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  --constraints
  , UNIQUE(athlete_profile_id, athlete_number)
);

--Developer Name: Jason Lyle
--Functionality: Triggers to automatically create the primary key and set the
--	date updated column
--Table and Column comments
--Triggers
CREATE OR REPLACE TRIGGER PM_Team_Athlete_bi
BEFORE INSERT ON PM_Team_Athlete
FOR EACH ROW
WHEN (new.Team_Athlete_id IS NULL)
BEGIN
  SELECT Sys_Guid() INTO :new.Team_Athlete_id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER PM_Team_Athlete_bu
BEFORE INSERT ON PM_Team_Athlete
FOR EACH ROW
BEGIN
  SELECT SYSTIMESTAMP INTO :new.std_dt_updated FROM dual;
END;
/

--------------------------------------------------------------------------------
--Developer Name: Jason Lyle
--Functionality: A relation to hold team entities that have not yet been saved.
CREATE TABLE PM_Team_Profile_WORKING(
  --PRIMARY KEY columns
    Team_Profile_WORKING_id VARCHAR2(32) DEFAULT Sys_Guid() PRIMARY KEY
  --OTHER columns
  --Basic information
  , team_name VARCHAR2(100) NOT NULL
  , picture BLOB
  , school_name VARCHAR2(100)
  , conference VARCHAR2(50)
  , cheer VARCHAR2(4000)
  , notes VARCHAR2(4000)
  --Team statistics
  , wins NUMBER
  , losses NUMBER
  , win_loss_score_diff NUMBER
  , espn_ranking NUMBER
  , rpi NUMBER
  --System information
  , text_color VARCHAR2(7) DEFAULT 'black' CHECK(text_color IN ('blue', 'red',
  		'yellow', 'black', 'magenta', 'green'))
  --FOREIGN KEY columns
  , profile_manager_id VARCHAR2(32) NOT NULL
  		REFERENCES PM_Profile_Manager(profile_manager_id)
  --STANDARD LEAD columns
  , std_dt_created TIMESTAMP DEFAULT systimestamp
  , std_dt_updated TIMESTAMP DEFAULT systimestamp
  , std_user_created VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  , std_user_updated VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  --constraints
);

--Developer Name: Jason Lyle
--Functionality: Triggers to automatically create the primary key and set the
--	date updated column
--Table and Column comments
--Triggers
CREATE OR REPLACE TRIGGER PM_Team_Profile_WORKING_bi
BEFORE INSERT ON PM_Team_Profile_WORKING
FOR EACH ROW
WHEN (new.Team_Profile_WORKING_id IS NULL)
BEGIN
  SELECT Sys_Guid() INTO :new.Team_Profile_WORKING_id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER PM_Team_Profile_WORKING_bu
BEFORE INSERT ON PM_Team_Profile_WORKING
FOR EACH ROW
BEGIN
  SELECT SYSTIMESTAMP INTO :new.std_dt_updated FROM dual;
END;
/

--------------------------------------------------------------------------------
--Developer Name: Jason Lyle
--Functionality: A relation to hold athlete entities that have not yet been
--	saved.
CREATE TABLE PM_Athlete_Profile_WORKING(
  --PRIMARY KEY columns
    Athlete_Profile_WORKING_id VARCHAR2(32) DEFAULT Sys_Guid() PRIMARY KEY
  --OTHER columns
  --Basic information
  , first_name VARCHAR2(50) NOT NULL
  , middle_initial CHAR
  , last_name VARCHAR2(50) NOT NULL
  , nickname VARCHAR2(50)
  , picture BLOB
  , class_rank CHAR(2) CHECK(class_rank IN ('fr', 'so', 'jr', 'sr', 'ss',
  		'gr', 'ot'))
  , notes VARCHAR2(4000)
  --Player statistics
  , points_per_game NUMBER
  , assists_per_game NUMBER
  , rebounds_per_game NUMBER
  , mins_per_game NUMBER
  , steals_per_game NUMBER
  , free_throw_percentage NUMBER
  --System information
  , text_color VARCHAR2(7) DEFAULT 'black' CHECK (text_color IN ('blue', 'red',
  		'yellow', 'black', 'magenta', 'green'))
  --FOREIGN KEY columns
  , profile_manager_id VARCHAR2(32) NOT NULL
  		REFERENCES PM_Profile_Manager(profile_manager_id)
  --STANDARD LEAD columns
  , std_dt_created TIMESTAMP DEFAULT systimestamp
  , std_dt_updated TIMESTAMP DEFAULT systimestamp
  , std_user_created VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  , std_user_updated VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  --constraints
);

--Developer Name: Jason Lyle
--Functionality: Triggers to automatically create the primary key and set the
--	date updated column
--Table and Column comments
--Triggers
CREATE OR REPLACE TRIGGER PM_Athlete_Profile_WORKING_bi
BEFORE INSERT ON PM_Athlete_Profile_WORKING
FOR EACH ROW
WHEN (new.Athlete_Profile_WORKING_id IS NULL)
BEGIN
  SELECT Sys_Guid() INTO :new.Athlete_Profile_WORKING_id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER PM_Athlete_Profile_WORKING_bu
BEFORE INSERT ON PM_Athlete_Profile_WORKING
FOR EACH ROW
BEGIN
  SELECT SYSTIMESTAMP INTO :new.std_dt_updated FROM dual;
END;
/

--------------------------------------------------------------------------------
--Developer Name: Jason Lyle
--Functionality: A relation representing the relations between a team and an
-- 	athlete that have not been saved yet.
CREATE TABLE PM_Team_Athlete_WORKING(
  --PRIMARY KEY columns
    Team_Athlete_WORKING_id VARCHAR2(32) DEFAULT Sys_Guid() PRIMARY KEY
  --OTHER columns
  , athlete_number NUMBER
  --FOREIGN KEY columns
  , team_profile_id VARCHAR2(32) NOT NULL
  , athlete_profile_id VARCHAR2(32) NOT NULL
  --STANDARD LEAD columns
  , std_dt_created TIMESTAMP DEFAULT systimestamp
  , std_dt_updated TIMESTAMP DEFAULT systimestamp
  , std_user_created VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  , std_user_updated VARCHAR2(50) DEFAULT 'unknown' NOT NULL
  --constraints
  , UNIQUE(athlete_profile_id, athlete_number)
);

--Developer Name: Jason Lyle
--Functionality: Triggers to automatically create the primary key and set the
--	date updated column
--Table and Column comments
--Triggers
CREATE OR REPLACE TRIGGER PM_Team_Athlete_WORKING_bi
BEFORE INSERT ON PM_Team_Athlete_WORKING
FOR EACH ROW
WHEN (new.Team_Athlete_WORKING_id IS NULL)
BEGIN
  SELECT Sys_Guid() INTO :new.Team_Athlete_WORKING_id FROM dual;
END;
/

CREATE OR REPLACE TRIGGER PM_Team_Athlete_WORKING_bu
BEFORE INSERT ON PM_Team_Athlete_WORKING
FOR EACH ROW
BEGIN
  SELECT SYSTIMESTAMP INTO :new.std_dt_updated FROM dual;
END;
/
